package bibbletex.controllers;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.UUID;

import bibbletex.helpers.SQLHelper;
import bibbletex.models.Reference;

public class ReferenceController {
	/**
	 * This method saves a given reference in the database
	 * @param r Reference to save
	 * @return Whether the operation was successful
	 */
	public static boolean save(Reference r) {
		ResultSet rs = null;
		boolean success = false;

		// if exists, update, if not, insert
		rs = SQLHelper.execQuerySQL("select * from reference where id = '"
				+ r.getId() + "'");

		try {
			if (rs.next()) {
				// Update
				success = SQLHelper.execSQL("update reference set "
						+ "id = '" + r.getId() 
						+ "', type = '" + r.getType()
						+ "', title = '" + r.getTitle() 
						+ "', author = '" + r.getAuthors()
						+ "', label = '" + r.getLabel() 
						+ "', title = '" + r.getTitle() 
						+ "', author = '" + r.getAuthors() 
						+ "', booktitle = '" + r.getBooktitle() 
						+ "', editor = '" + r.getEditor() 
						+ "', publisher = '" + r.getPublisher() 
						+ "', organization = '" + r.getOrganization() 
						+ "', school = '" + r.getSchool() 
						+ "', journal = '" + r.getJournal() 
						+ "', year = '" + r.getYear() 
						+ "', volume = '" + r.getVolume() 
						+ "', number = '" + r.getNumber() 
						+ "', pages = '" + r.getPages() 
						+ "', month = '" + r.getMonth() 
						+ "', note = '" + r.getNote() 
						+ "', series = '" + r.getSeries() 
						+ "', address = '" + r.getAddress() 
						+ "', edition = '" + r.getEdition() 
						+ "', isbn = '" + r.getIsbn() 
						+ "', howpublished = '" + r.getHowpublished() 
						+ "', suggestion = '"
						+ (r.is_suggestion()? "1" : "0") + "' where id = '" + r.getId() + "'");
			} else {
				// Insert
				success = SQLHelper.execSQL("insert into reference (id,label,"
						+ "type,title,author,booktitle, editor, publisher,"
						+ "organization, school, journal, year, volume, number,"
						+ "pages, month, note, series, address, edition, isbn, "
						+ "howpublished,suggestion)values('"
						+ r.getId() + "', '"
						+ r.getLabel() + "', '"
						+ r.getType() + "', '"
						+ r.getTitle() + "', '" 
						+ r.getAuthors() + "', '"
						+ r.getBooktitle() + "', '"
						+ r.getEditor() + "', '"
						+ r.getPublisher() + "', '"
						+ r.getOrganization() + "', '"
						+ r.getSchool() + "', '"
						+ r.getJournal() + "', '"
						+ r.getYear() + "', '"
						+ r.getVolume() + "', '"
						+ r.getNumber() + "', '"
						+ r.getPages() + "', '"
						+ r.getMonth() + "', '"
						+ r.getNote() + "', '"
						+ r.getSeries() + "', '"
						+ r.getAddress() + "', '"
						+ r.getEdition() + "', '"
						+ r.getIsbn() + "', '"
						+ r.getHowpublished() + "', '"
						+ (r.is_suggestion()? "1" : "0") + "')");
			}
		} catch (SQLException e) {
			System.err.println("Reference.save: " + e);
		}

		return success;
	}

	/**
	 * Delete a reference from the database
	 * @param r Reference to delete
	 * @return Whether the operation was successful
	 */
	public static boolean delete(Reference r) {
		return SQLHelper.execSQL("delete from reference where id='" + r.getId()
				+ "'");
	}
	
	public static boolean delete(String reference_id) {
		return SQLHelper.execSQL("delete from reference where id='" + reference_id
				+ "'");
	}

	/**
	 * This method loads the reference with the given ID from the database
	 * @param id UUID of the reference to load
	 * @return Loaded Reference
	 */
	public static Reference load(String id) {
		Reference r = null;
		ResultSet rs = null;

		try {
			rs = SQLHelper.execQuerySQL("select * from reference where id='"
					+ id + "'");

			if (rs.next()) {
				r = new Reference(UUID.fromString(rs.getString("id")));
				r.setType(rs.getString("type"));
				r.setTitle(rs.getString("title"));
				r.setAuthors(rs.getString("author"));
				r.setLabel(rs.getString("label"));
				r.setBooktitle(rs.getString("booktitle")); 
				r.setEditor(rs.getString("editor"));
				r.setPublisher(rs.getString("publisher")); 
				r.setOrganization(rs.getString("organization")); 
				r.setSchool(rs.getString("school"));
				r.setJournal(rs.getString("journal")); 
				r.setYear(rs.getString("year"));
				r.setVolume(rs.getString("volume")); 
				r.setNumber(rs.getString("number"));
				r.setPages(rs.getString("pages"));
				r.setMonth(rs.getString("month"));
				r.setNote(rs.getString("note"));
				r.setSeries(rs.getString("series")); 
				r.setAddress(rs.getString("address")); 
				r.setEdition(rs.getString("edition"));
				r.setIsbn(rs.getString("isbn"));
				r.setHowpublished(rs.getString("howpublished")); 
				r.setIs_suggestion(rs.getString("suggestion").equals("1"));
			}

		} catch (SQLException e) {
			System.err.println("Reference.load: " + e);
		}

		return r;
	}
}
